package com.vd.tbsms.parse;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


public class XlsParse {
	private List<String> headers;
	
	public List<String> getHeaders() {
		return headers;
	}



	private Log log = LogFactory.getLog(XlsParse.class);

	@SuppressWarnings("unchecked")
	public List<Map> parse(Workbook book, String sheetName) {
		log.info("parse start:sheetName" + sheetName);
		if (sheetName == null || sheetName.isEmpty()) {
			sheetName = "Sheet1";
		}
		Sheet sheet = book.getSheet(sheetName);
		if (sheet == null) {
			sheet = book.getSheetAt(0);
		}
		return parse(sheet);
	}

	

	@SuppressWarnings("unchecked")
	public List<Map> parse(Sheet sheet) {
		Row headRow = sheet.getRow(0);
		headers=new ArrayList<String>();
		for(int i=headRow.getFirstCellNum();i<headRow.getLastCellNum();i++){
			String header= headRow.getCell(i).getStringCellValue().trim();
			log.info("add header"+header);
			headers.add(header);
		}
		//headers.add("批注");
		List<Map> list = new ArrayList<Map>(300);
		for(int j= 1;j<=sheet.getLastRowNum();j++){
			Row row = sheet.getRow(j);
			Map map = new LinkedHashMap();
			StringBuffer sb = new StringBuffer();
			for(int i=0;i<headers.size();i++){
				Cell cell = row.getCell(i);
				Object cellValue;
				if(cell.getCellType()==Cell.CELL_TYPE_STRING){
					cellValue=cell.getStringCellValue();
				}else if (cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
					 if (HSSFDateUtil.isCellDateFormatted(cell)) {  
						 cellValue = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
					 }else{
						 cellValue=String.valueOf(cell.getNumericCellValue());
					 }
				}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
					cellValue = cell.getBooleanCellValue()?"是":"否";
				}else if (cell.getCellType()==Cell.CELL_TYPE_FORMULA){
					cellValue=String.valueOf(new BigDecimal(String.valueOf(cell.getNumericCellValue())).setScale(2,
							BigDecimal.ROUND_HALF_UP));
				}else{
					cellValue="";
				}
				if(cell.getCellComment()!=null){
					String cmts = cell.getCellComment().getString().toString();
					sb.append(cmts+";");
				}
				map.put(headers.get(i), cellValue);
			}
			map.put("批注", sb.toString());
			list.add(map);
		}
		return list;
	}
}
